﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
namespace BanHangCshap.Class
{
    class BaoGia:Database
    {
        #region ***** Fields & Properties *****
        private string _mabaogia;
        public string mabaogia
        {
            get
            {
                return _mabaogia;
            }
            set
            {
                _mabaogia = value;
            }
        }
        private string _ngaybaogia;
        public string ngaybaogia
        {
            get
            {
                return _ngaybaogia;
            }
            set
            {
                _ngaybaogia = value;
            }
        }
        private string _ghichu;
        public string ghichu
        {
            get
            {
                return _ghichu;
            }
            set
            {
                _ghichu = value;
            }
        }
        private string _tendn;
        public string tendn
        {
            get
            {
                return _tendn;
            }
            set
            {
                _tendn = value;
            }
        }
        private int _loaibaogia;
        public int loaibaogia
        {
            get
            {
                return _loaibaogia;
            }
            set
            {
                _loaibaogia = value;
            }
        }
        #endregion

        #region ***** Init Methods *****
        public BaoGia()
        {
        }
        public BaoGia(string mabaogia)
        {
            this.mabaogia = mabaogia;
        }
        //public BaoGia(string mabaogia, DateTime ngaybaogia, string ghichu, string tendn)
        //{
        //    this.mabaogia = mabaogia;
        //    this.ngaybaogia = ngaybaogia;
        //    this.ghichu = ghichu;
        //    this.tendn = tendn;
        //}
        #endregion
        public Boolean isExistBaoGia(string mabaogia)
        {
            string sql = "select * from BaoGia where mabaogia = '" + mabaogia + "'";
            return isExist(sql);
        }
        public DataTable layDSBaoGia()
        {
            string sql = "select * from BaoGia order by mabaogia desc";
            return getDataTable(sql);
        }
        public DataTable layThongTinBaoGia(string mabaogia)
        {
            string sql = "select * from BaoGia  where mabaogia = '" + mabaogia + "' ";
            return getDataTable(sql);
        }
        public Boolean xoaBaoGia(string mabaogia)
        {
            string sql = "delete BaoGia  where mabaogia = '" + mabaogia + "'";
            return executeQueryTrans(sql);
        }
        public Boolean themBaoGia()
        {
            string sql = "insert into BaoGia(mabaogia,ngaybaogia,loaibaogia,ghichu,tendn) values ('" + mabaogia + "','" + ngaybaogia + "'," + loaibaogia + ",N'" + ghichu + "','" + tendn + "')";
            return executeQueryTrans(sql);
            
        }
        public Boolean themBaoGiaChiTiet(string mabaogia, string mahang, string motagb, double dongia, string ghichubg)
        {
            string sql = "insert into BaoGiaChiTiet values ('" + mabaogia + "','" + mahang + "',N'" + motagb + "'," + dongia + ",N'" + ghichubg + "')";
            return executeQueryTrans(sql);
        }
        public DataTable layDSBaoGiaChiTiet(string mabaogia)
        {
            string sql = "select b.maloai,a.mahang, tenhang, dvt,motabg, dongia, ghichubg ";
            sql += "from BaoGiaChiTiet a, HangHoa b where a.mahang = b.mahang and mabaogia = '" + mabaogia + "' order by maloai,tenhang";
            return getDataTable(sql);
        }
        public DataTable chonSanPhamBaoGia(int loaibaogia)
        {
            string gia = "";
            if (loaibaogia == 1)
                gia = "giaxuatle";
            else gia = "giaxuatsi";
            string sql = " select convert(bit,'false') chon,maloai, mahang, tenhang, dvt,mota as motabg, " + gia + " as dongia, '' as ghichubg from HangHoa  order by maloai";
            return getDataTable(sql);
        }
        public double laySoBaoGiaTrongNgay(string ngay)
        {
            string sql = "SELECT max(convert(int,substring(mabaogia,12,len(mabaogia)-10))) FROM BaoGia WHERE convert(varchar(10),ngaybaogia,103)='" + ngay + "'  ";
            return getNumberValue(sql);
        }
        public DataTable inBaoGia(string tendoanhnghiep, string diachi, string dienthoai,string mabaogia)
        {
            string sql = "select  distinct N'" + tendoanhnghiep + "' as tendoanhnghiep, N'" + diachi + "' as diachi, N'" + dienthoai + "' as dienthoai,c.maloai,tenloai,b.mahang,tenhang,dvt,motabg,dongia,ghichubg,a.ghichu,N'Ngày ' + case when len(convert(char(2),day(convert(datetime,ngaybaogia,103)))) = 1 then '0' + convert(char(2),day(convert(datetime,ngaybaogia,103))) else convert(char(2),day(convert(datetime,ngaybaogia,103))) end + N' tháng ' + case when len(convert(char(2),month(convert(datetime,ngaybaogia,103)))) = 1 then '0' + convert(char(2),month(convert(datetime,ngaybaogia,103))) else convert(char(2),month(convert(datetime,ngaybaogia,103))) end  + N' năm ' + convert(char(4),year(ngaybaogia)) as ngaybaogia ";
            sql += "from BaoGia a, BaoGiaChiTiet b,HangHoa c, LoaiHang d ";
            sql += "where a.mabaogia = '" + mabaogia + "' and a.mabaogia = b.mabaogia and b.mahang = c.mahang and c.maloai = d.maloai order by maloai,tenhang";
            return getDataTable(sql);
        }
    }
}
